package com.bupt.ilink.mapper.innovationPlaza;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.bupt.ilink.entity.innovationPlaza.InnovationTags;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigInteger;
import java.util.List;

@Mapper
public interface InnovationTagsMapper extends BaseMapper<InnovationTags> {

    @Select("SELECT it.name FROM innovation_tags it " +
            "JOIN innovation_connect_tags ict ON it.tag_id = ict.tag_id " +
            "WHERE ict.project_id = #{projectId}")
    List<String> getTagsByProjectId(Long projectId);

    //查询现有tag
    @Select("SELECT COUNT(*) FROM innovation_tags " +
            "WHERE tag_type = #{tagType} AND name = #{name}")
    int checkTagExists(@Param("tagType") String tagType, @Param("name") String name);

    @Select({
            "<script>",
            "SELECT DISTINCT project_id",
            "FROM innovation_connect_tags",
            "WHERE tag_id IN (",
            "    SELECT tag_id",
            "    FROM innovation_tags",
            "    WHERE name IN",
            "    <foreach item='tag' collection='tags' open='(' separator=',' close=')'>",
            "        #{tag}",
            "    </foreach>",
            ")",
            "</script>"
    })
    List<Long> getProjectIdsByTags(@Param("tags") List<String> tags);

    @Select("SELECT it.tag_id, it.name, it.tag_type FROM innovation_tags it " +
            "JOIN innovation_connect_tags ict ON it.tag_id = ict.tag_id " +
            "WHERE it.tag_type = 'Competition' " +
            "GROUP BY it.tag_id, it.name, it.tag_type " +
            "ORDER BY COUNT(ict.tag_id) DESC " +
            "LIMIT 8;")
    List<InnovationTags> selectCommonCompetitionTags();

    @Select("SELECT it.tag_id, it.name, it.tag_type FROM innovation_tags it " +
            "JOIN innovation_connect_tags ict ON it.tag_id = ict.tag_id " +
            "WHERE it.tag_type = 'project' " +
            "GROUP BY it.tag_id, it.name, it.tag_type " +
            "ORDER BY COUNT(ict.tag_id) DESC " +
            "LIMIT 8;")
    List<InnovationTags> selectCommonProjectTags();
}